package com.limao.DBProvider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.limao.DBDataClass.AdvDataClass;
import com.limao.DbConnection.DbConnectionManager;

public class CrowdAdvProvider {

	private static class DB_Adv_ProviderContainer {
		private static CrowdAdvProvider instance = new CrowdAdvProvider();
	}

	public static CrowdAdvProvider getInstance() {
		return DB_Adv_ProviderContainer.instance;
	}

	// 欢迎页
	private static final String cd_sys_welcomeadv = "SELECT * FROM cd_sys_adv where showpos=0 and state=1 order by ctime desc limit 1"; 
	// 有效的系统素材
	private static final String cd_sysMatter = "SELECT * FROM cd_sys_adv where showpos=1 and state=1 order by advsort desc"; 
	
	
	// 初始化
	private CrowdAdvProvider() {

	}

	
	private AdvDataClass encapOrderData(ResultSet rs) throws Exception{
		
		AdvDataClass orderData = new AdvDataClass();
		
		orderData.advid = rs.getString("advid");
		orderData.showpos = rs.getString("showpos");
		orderData.imgurl = rs.getString("imgurl");
		orderData.type = rs.getString("type");
		orderData.weburl = rs.getString("weburl");
		orderData.projectid = rs.getString("projectid");
		orderData.state = rs.getString("state");
		orderData.advtxt = rs.getString("advtxt");
		orderData.advsort = rs.getString("advsort");
		orderData.ctime = rs.getString("ctime");

		return orderData;		

	}

	//&新鲜宝  系统 欢迎页广告图
	public AdvDataClass Load_welcomeAdv() {
		AdvDataClass obj = new AdvDataClass();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(cd_sys_welcomeadv);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				obj=encapOrderData(rs);
			}
		} catch (Exception e) {
			obj=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return obj;

	}

	
	//&新鲜宝  系统 众筹首页广告全部
	public ArrayList<AdvDataClass> Load_SySMatter_All() {
		
		ArrayList<AdvDataClass> list = new ArrayList<AdvDataClass>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(cd_sysMatter);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(encapOrderData(rs));
			}
		} catch (Exception e) {
			list=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return list;

	}
	

}
